package tom.server;

import java.io.BufferedReader;
import java.io.FileReader;
import java.io.IOException;
import java.sql.*;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.HashMap;
import java.util.Locale;
import java.util.Map;

import javax.swing.JOptionPane;

public class Database {

	private static Calendar cal;

	private static String thisYear;

	static {
		Locale.setDefault(Locale.UK);
		cal = Calendar.getInstance(Locale.UK);
		thisYear = String.valueOf(cal.get(Calendar.YEAR));

	}

	public static final String DATE_FORMAT = "dd/MM/yyyy";

	public static final String DERBY_DATE_FORMAT = "yyyy-MM-d";

	public static SimpleDateFormat dateFormat = new SimpleDateFormat(
			DATE_FORMAT, Locale.UK);

	public static SimpleDateFormat derbyDateFormat = new SimpleDateFormat(
			DERBY_DATE_FORMAT, Locale.UK);

	public String createDerbyDateString(String s) {
		String x = null;
		try {
			x = derbyDateFormat.format(dateFormat.parse(s));
		} catch (ParseException e) {
			e.printStackTrace();
		}
		return x;
	}

	public String createDerbyDateString(java.util.Date d) {
		return derbyDateFormat.format(d);
	}

	public Date createDate(String s) {
		Date d = null;
		try {
			d = createDate(dateFormat.parse(s));
		} catch (ParseException e) {
			JOptionPane.showMessageDialog(null, e.getMessage());
		}
		return d;
	}

	public static Date createDate(java.util.Date date) {
		return new Date(date.getTime());
	}

	public static Date createDate(int year, int month, int day) {
		java.sql.Date date = null;
		try {
			cal.set(year, month - 1, day);
			date = new java.sql.Date(cal.getTimeInMillis());
		} catch (Exception e) {
			JOptionPane.showMessageDialog(null, e.getMessage());
		}
		return date;
	}

	public static String completeDate(String dateTxt) {
		int len = dateTxt.length();
		String s = dateTxt;
		if (len >= 3 && len < 8) {
			if (dateTxt.charAt(len - 1) == '-')
				s = dateTxt + thisYear;
			else
				s = dateTxt + "-" + thisYear;
		}
		return s;
	}

	public static Database connectDatabase(String dbName, String user,
			String pswd) {
		Database dbConn = null;
		try {
			dbConn = new Database(new com.mysql.jdbc.Driver(),
					"jdbc:mysql://127.0.0.1:3306/" + dbName
							+ "?characterEncoding=TIS620", user, pswd);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return dbConn;
	}

	private Connection conn;

	private Statement stmt;

	private Map<String, PreparedStatement> sqlMap;

	// public static final Font FONT = new Font("Tahoma", Font.PLAIN, 16);

	public void close() {
		try {
			sqlMap.clear();
			stmt.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void createSQL(String name, String sql) {
		//if (sqlMap.get(name) == null) {
			try {
				PreparedStatement pstmt = conn.prepareStatement(sql);
				sqlMap.put(name, pstmt);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		//}
	}

	public PreparedStatement getStatement(String name) {
		return sqlMap.get(name);
	}

	public Database(Driver drvMan, String url, String user, String passwd)
			throws SQLException {
		DriverManager.registerDriver(drvMan);
		java.util.Properties prop = new java.util.Properties();
		if (user != null)
			prop.put("user", user);
		if (passwd != null)
			prop.put("password", passwd);

		conn = DriverManager.getConnection(url, prop);
		conn.setAutoCommit(true);
		stmt = conn.createStatement();
		sqlMap = new HashMap<String, PreparedStatement>();
	}

	public Database(Driver drvMan, String confile) throws SQLException {
		this(drvMan, Database.readConnectURL(confile), null, null);
	}

	public Connection getConnection() {
		return conn;
	}

	public ResultSet executeQuery(String query) {
		ResultSet rs = null;
		try {
			rs = conn.createStatement().executeQuery(query);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return rs;
	}

	public int execSqlMap(String name, Object[] params) throws SQLException {
		PreparedStatement stmt = sqlMap.get(name);
		int row = 0;
		if (stmt != null)
			if (params != null) {
				for (int i = 0; i < params.length; i++) {
					stmt.setObject(i + 1, params[i]);
				}
			}
		row = stmt.executeUpdate();
		return row;
	}

	public ResultSet querySqlMap(String name, Object[] params) {
		PreparedStatement stmt = sqlMap.get(name);
		ResultSet rs = null;
		if (stmt != null)
			try {
				if (params != null) {
					for (int i = 0; i < params.length; i++) {
						stmt.setObject(i + 1, params[i]);
					}
				}
				rs = stmt.executeQuery();
			} catch (SQLException e) {
				//JOptionPane.showMessageDialog(null, e.getMessage());
			}
		return rs;
	}

	public ResultSet querySqlMap(String name) {
		return querySqlMap(name, null);
	}

	public int executeUpdate(String sql) {
		int rowEffected = 0;
		try {
			rowEffected = stmt.executeUpdate(sql);
		} catch (SQLException e) {
			JOptionPane.showMessageDialog(null, e.getMessage() + "\n" + sql);
		}
		return rowEffected;
	}

	public static String readConnectURL(String confile) {
		String connectURL = null;
		try {
			BufferedReader bf = new BufferedReader(new FileReader(confile));
			connectURL = bf.readLine();
			bf.close();
		} catch (IOException e) {
			e.printStackTrace();
		}
		return connectURL;
	}

	public static void print(Object[] record) {
		for (int i = 0; i < record.length; i++)
			System.out.print("[" + record[i] + "]");
		System.out.println();
	}
	

}
